# -*- coding: utf-8 -*-

'''
@author: Luís Fonte nº6043
@date 03/12/2012
@obs:
Creates statistics and writes them on csv flie
'''

import xlrd
import sqlalchemy
from sqlalchemy.orm import sessionmaker
from sqlalchemy import *
import csv
import pylab as p



"""
# First Query: Courses with terms Computadores and Informática in association with establishment unity and years in activity
"""
def firstQuery():
    writer = csv.writer(open('FirstQuery.csv', "wb"))
    
    # Statistics completion form found and based on forum:  http://stackoverflow.com/questions/3618690/how-to-query-a-table-in-sqlalchemy
    # and Query Alchemy documentation:  http://docs.sqlalchemy.org/ru/latest/orm/query.html

    # Connecttion do database
    try:
        engine = create_engine('sqlite:///inscritos.db', echo=False) # False cause there's no need to print information of engine creation
    except IOError:
        print "Error acessing database file"
    metadata = MetaData(engine)
    # Definition of the table on database
    tabelaInscritos = Table('inscritos', metadata, autoload = True)
     
    # create a Session
    Session = sessionmaker(bind=engine)
    session = Session()
     
    
    # MAIN QUERY - searches for every row that contains the words "Computadores" and "Informática" on Curso_Superior column
    query = session.query(tabelaInscritos). filter(tabelaInscritos.c.Curso_Superior.like(u'%Computadores%'), tabelaInscritos.c.Curso_Superior.like(u'%Informática%')) # c de column
    

    listIndexOfYears = [] # list with indexes of years that each course was in activity -> row number of each year
    sequenceOfYears = ''  # string that is used to fill each position of listIndexOfYears

    
    # Creates a list with columns indexes of years that a course was in activity, to transform later on string and year description
    for row in query:
        for column in range(6, len(row)):
            if (row[column] != '-') & (row[column] != ''): # verifies that cell value is not blank or with "-"
                if sequenceOfYears == '':
                    sequenceOfYears = str(column)   # first column index wrote on string
                else:
                    sequenceOfYears += ',' + str(column)  # the other columns wrote on string
                    pass
                pass
            pass
        listIndexOfYears.append(sequenceOfYears)  # after string with with column indexes created, appends list with it
        sequenceOfYears = ''  # resets the temporary string
        pass

    # list with the description of each year and on the right position like database
    listYearsDescription = ['','','','','','','1995/96','1996/97','1997/98','1998/99','1999/00','2000/01','2001/02','2002/03','2003/04','2004/05','2005/06','2006/07','2007/08','2008/09','2009/10','2010/11']

    listOfYearsInActivity = [] # final list with the years that courses was open and in activity to add to query output

    stringWithYears = ''  # temporary string to help filling listYearsDescription
    
    # Transforms list of indexes to list with years description
    for row in range(len(listIndexOfYears)):
        tempList = listIndexOfYears[row].split(',') # slit indexes to a list
        for index in range(len(tempList)):
            i = int(tempList[index]) # the number that corresponds to a year description on list of years
            if stringWithYears == '':
                stringWithYears = listYearsDescription[i] # first year wrote on string
                pass
            stringWithYears += '_' + listYearsDescription[i] # the other years of a row, with ',' on formatation
            pass
        listOfYearsInActivity.append(stringWithYears) # after string with years created, appends list with it
        stringWithYears = '' # resets the temporary string
        pass

    t=0
    # Creation of the listForCSV with query results
    for row in query:
        stringToWrite = [row[4].encode('latin-1')+ ',' + row[1].encode('latin-1') + ',' + row[2].encode('latin-1') + ',' + listOfYearsInActivity[t].encode('latin-1')] #formats the string to write on csv
        writer.writerow(stringToWrite) # writes current string on csv
        t+=1
        pass


"""
# Number of students on a course per year
"""
def secondQuery():
    writer = csv.writer(open('SecondQuery.csv', "wb"))
    
    # Statistics completion form found and based on forum:  http://stackoverflow.com/questions/3618690/how-to-query-a-table-in-sqlalchemy
    # and Query Alchemy documentation:  http://docs.sqlalchemy.org/ru/latest/orm/query.html

    # Connecttion do database
    try:
        engine = create_engine('sqlite:///inscritos.db', echo=False) # False cause there's no need to print information of engine creation
    except IOError:
        print "Error acessing database file"
    metadata = MetaData(engine)
    # Definition of the table on database
    tabelaInscritos = Table('inscritos', metadata, autoload = True)
     
    # create a Session
    Session = sessionmaker(bind=engine)
    session = Session()

    # MAIN QUERY - searches for every row that contains the words "Computadores" and "Informática" on Curso_Superior column
    query = session.query(tabelaInscritos). filter(tabelaInscritos.c.Curso_Superior.like(u'%Computadores%'), tabelaInscritos.c.Curso_Superior.like(u'%Informática%'))

    # creates list rows with course and number of students per year
    for row in query:
        rowToWrite = [row[4].encode('latin-1')+ ',' + row[6].encode('latin-1')+ ',' + row[7].encode('latin-1')+ ',' + row[8].encode('latin-1')+ ',' +
                         row[9].encode('latin-1')+ ',' + row[10].encode('latin-1')+ ',' + row[11].encode('latin-1')+ ',' + row[12].encode('latin-1')+ ',' +
                         row[13].encode('latin-1')+ ',' + row[14].encode('latin-1')+ ',' + row[15].encode('latin-1')+ ',' + row[16].encode('latin-1')+ ',' +
                         row[17].encode('latin-1')+ ',' + row[18].encode('latin-1')+ ',' + row[19].encode('latin-1')+ ',' + row[20].encode('latin-1')+ ','+
                         row[21].encode('latin-1')]
        writer.writerow(rowToWrite) # writes current row from list on csv
        pass

    pass

"""
# Method that do query and returns a row with formation level data
# @param formation_type - string with formation description
# @param number_query - specify wich query is, 3rd or 4th
"""
def doQuery(formation_type, number_query):

    
    # Statistics completion form found and based on forum:  http://stackoverflow.com/questions/3618690/how-to-query-a-table-in-sqlalchemy
    # and Query Alchemy documentation:  http://docs.sqlalchemy.org/ru/latest/orm/query.html

    # Connecttion do database
    try:
        engine = create_engine('sqlite:///inscritos.db', echo=False) # False cause there's no need to print information of engine creation
    except IOError:
        print "Error acessing database file"
    metadata = MetaData(engine)
    # Definition of the table on database
    tabelaInscritos = Table('inscritos', metadata, autoload = True)
     
    # create a Session
    Session = sessionmaker(bind=engine)
    session = Session()

    # QUERY - searches for every row that contains the words "Computadores" and "Informática" on Curso_Superior column and formation_type on Nivel_de_Formacao column
    query = session.query(tabelaInscritos). filter(tabelaInscritos.c.Curso_Superior.like(u'%Computadores%'), tabelaInscritos.c.Curso_Superior.like(u'%Informática%'), tabelaInscritos.c.Nivel_de_Formacao.like(formation_type))

    if number_query == 3:
        stringQuery = fillList_ThirdQuery(query)  # string with data given and treated from query
        pass
    if number_query == 4:
        stringQuery = fillList_FourthQuery(query)

    rowToWrite = [query[0][3].encode('latin-1') +','+ stringQuery.encode('latin-1')] # codifies the string from query on a codification accepted by csv files and that not changes characters
    
    return rowToWrite

    pass
    
"""
# Method can do 2 querys, by specifying wich one by parameter number_query
# Number of courses by formation level per year
# Or
# Number os students by formation level per year
"""
def thirdQuery_Or_FourthQuery(number_query):   

    #Formations
        #P - Preparatórios de licenciatura
        #PL - Preparatório de licenciatura 1ºciclo
        #L - Licenciatura
        #L1 - Licenciatura - 1º ciclo
        #M2 - Mestrado - 2º ciclo
        #D - Doutoramento
        #D3 - Doutoramento - 3º ciclo
        #LB - Bacharelato+Licenciatura

    if number_query == 3:
        writer = csv.writer(open('ThirdQuery.csv', "wb"))
        pass
    if number_query == 4:
        writer = csv.writer(open('FourthQuery.csv', "wb"))

    # Statistics completion form found and based on forum:  http://stackoverflow.com/questions/3618690/how-to-query-a-table-in-sqlalchemy
    # and Query Alchemy documentation:  http://docs.sqlalchemy.org/ru/latest/orm/query.html

    # Connecttion do database
    try:
        engine = create_engine('sqlite:///inscritos.db', echo=False) # False cause there's no need to print information of engine creation
    except IOError:
        print "Error acessing database file"
    metadata = MetaData(engine)
    # Definition of the table on database
    tabelaInscritos = Table('inscritos', metadata, autoload = True)
     
    # create a Session
    Session = sessionmaker(bind=engine)
    session = Session()

    listOfFormations = [u'P - Preparatórios de licenciatura',u'PL - Preparatório de licenciatura 1ºciclo',u'L - Licenciatura',u'L1 - Licenciatura - 1º ciclo',
                        u'M2 - Mestrado - 2º ciclo',u'D - Doutoramento',u'D3 - Doutoramento - 3º ciclo',u'LB - Bacharelato+Licenciatura']

    # list will contain every row to write
    listStat = []

    # adds to stat list every row from query
    for i in range(len(listOfFormations)):
        listStat.append(doQuery(listOfFormations[i], number_query))
        pass

    for o in range(len(listStat)):
        writer.writerow(listStat[o]) # writes a row on csv file with string from query
        pass
    pass


"""
# fill list with data formatted from a query
"""
def fillList_ThirdQuery(query):
    
    listFinal = [] # list with number of classes por year - Integer List
    stringQuery = ''    # string with number of classes por year to be returned by this function
    
    for row in query:
        listTmp = []
        for r in range(6,22):
            if row[r] != '-': # If the cell value is different of '-', then there are students registered on that curse, that year. So increments 1 class on that year, on that formation obtained from query
                listTmp.append(1)
            else:
                listTmp.append(0) # If its equal to '-', there is a closed class that year. So appends 0 to list
            pass
        
        if len(listFinal) != len(listTmp):
            listFinal = listTmp
        else:
            for i in range(len(listTmp)):  # creates the list with the final values of classes per year
                listFinal[i] += listTmp[i]
                pass
        pass
    
    for t in range(len(listFinal)):     # Passes list data to a single string, divided by commas
        if stringQuery == '':
            stringQuery = str(listFinal[t])
        else:
            stringQuery += ',' + str(listFinal[t])
            pass
        pass

    return stringQuery


"""
# Method used for all functions from 4th to fill list with data from years
"""
def fillList_FourthQuery(query):

    listFinal = []
    stringFinal = ''
    
    for row in query:
        listTmp = []
        for r in range(6,22):
            if row[r] != '-':  # If the cell value is different of '-', then there are students registered on that curse, that year. So adds number of students on that class on that year, to a temporary list
                listTmp.append(float(row[r]))
            else:
                listTmp.append(int(0))   # If its equal to '-', there is a closed class that year and there are no students on that class. So appends 0 to list
            pass
        
        if len(listFinal) != len(listTmp):
            listFinal = listTmp
        else:
            for i in range(len(listTmp)):   # creates the list with the final values of students on a formation passed through query given, per year
                listFinal[i] += listTmp[i]
                pass
        pass


    for o in range(len(listFinal)):     # Passes list data to a single string, divided by commas
        if stringFinal == '':
            stringFinal = str(listFinal[0])
        else:
            stringFinal += ',' + str(listFinal[o])

    return stringFinal

